淺談 SQL Server 的交易紀錄檔
TLDR
- 交易紀錄檔 (.ldf) 用於記錄所有資料變更，確保資料一致性與恢復能力。
- 復原模式決定了交易紀錄檔的截斷行為：簡單模式會自動截斷，完整與大量記錄模式則必須透過交易紀錄檔備份來截斷。
- 若選擇完整或大量記錄模式，務必定期執行交易紀錄檔備份，否則將導致 .ldf 檔案無限制增長。
- 壓縮檔案空間前，應先執行交易紀錄檔備份或切換至簡單模式，以釋放可回收空間。
- 建議備份策略：每週完整備份、每日差異備份、每 15 分鐘交易紀錄檔備份。
資料庫檔案機制
SQL Server 資料庫由兩類核心檔案組成：
- 主資料庫檔案 (.mdf)：儲存資料庫結構（Table, Index, View）與實際資料。
- 交易記錄檔 (.ldf)：記錄所有交易變更。當執行 Checkpoint 時，SQL Server 會將已提交的交易從 .ldf 寫入 .mdf。
復原模式與交易紀錄截斷
什麼情況下會遇到交易紀錄檔暴增？通常是因為設定了「完整」或「大量記錄」模式，卻未執行對應的備份作業。
- 完整 (Full)：記錄所有交易，支援點時間還原。檢查點不會自動截斷，需依賴備份。
- 大量記錄 (Bulk-Logged)：針對批量操作進行最小化記錄以提升效能，無法進行細粒度點時間還原。檢查點不會自動截斷，需依賴備份。
- 簡單 (Simple)：每次檢查點後自動截斷紀錄檔，無法進行交易紀錄檔備份，僅能還原至最近一次備份點。
TIP
無論採用何種復原模式，皆不影響 Begin Transaction 的語法使用。
備份策略建議
備份是管理 .ldf 檔案大小的關鍵手段：
- 完整備份：備份整個資料庫。
- 差異備份：僅備份自上次完整備份後的變更。
- 交易紀錄檔備份：備份自上次紀錄檔備份後的交易，並觸發截斷以重用空間。
實務建議：每週完整備份 + 每日差異備份 + 每 15 分鐘交易紀錄檔備份。
資料庫空間管理與壓縮
什麼情況下需要壓縮檔案？當資料庫已設定空間（Allocated Space）過大，且希望回收未使用的磁碟空間時。
空間管理原則
- 已設定空間：檔案在磁碟上的實際大小。
- 已使用空間：實際儲存資料的空間。
- 成長策略：若達到檔案大小上限且無剩餘空間，資料庫將無法寫入。
壓縮指令
若需釋放空間，可使用以下指令：
sql
-- 將檔案大小收縮到 1000 MB
DBCC SHRINKFILE ({檔案邏輯名稱}, 1000);注意事項：
- 壓縮限制為已使用空間的大小。
- 執行壓縮前，務必先進行交易紀錄檔備份，或將模式改為「簡單」以觸發截斷。
- 不建議對主資料檔案 (.mdf) 頻繁壓縮，可能導致交易紀錄檔異常暴增。
常見問題分析
什麼情況下會遇到備份策略失效？當資料庫選用「完整復原模式」卻忽略交易紀錄檔備份時，會產生以下負面影響：
- 交易紀錄檔無限制增長：持續消耗硬碟空間。
- 備份檔案過大：完整備份會包含所有未截斷的交易紀錄。
- 效能浪費：若未利用完整模式的備份優勢，改用簡單模式通常能獲得更好的效能。
異動歷程
- 初版文件建立。